The header provides an overview of the information on the database and host

AWR Header

Begin Snap and End Snap: The start and stop times of the analysis period

Elasped : Represents the time between the two snapshots.

DB TIME: Represents the activity on the database - time to answer user calls

Database time

  = sum (User response time) Time model

  = sum (CPU time + Wait time) Event model

DB time links the resource consumption with user response time.

Database load - How application was busy on doing database calls?

= DB time/Elapsed
= 38.71/120.09
= 0.32 (a.k.a average active sessions)

SCENARIO

CPUs: 12 Cores: 2 Sockets: This host has 12 cores.

Accordingly, in a 60 minute hour we have 60 X 12 = 720 CPU minutes

Therefore, for 2 hours we have 1440 CPU mins.

However, we used only 39 CPU mins - only 39/1440 = 2.7% of all available CPU on the box!

If the host was CPU bound DB, you would probably see DB CPU more like 900 - 1000 mins

Usually it indicates:

  1. Contention for latches
  2. SQL statements doing too many logical I/Os
  3. Lots of parsing due to the application not using bind variables

CURSORS/SESSIONS: If the number of Cursors/Session at the End Snap is greater than that at Begin Snap then we may have a cursor leak.

Review logons in the Load Profile and sessions in the initialization parameters.

NB: Open_cursors is per session and it does not have a DB-wide scope. Look at v$open_cursors

Look out for lock and session leakage.